package top.codedance.iotp.common.util.db;

import top.codedance.iotp.common.entity.DeviceEntity;
import top.codedance.iotp.common.util.AsymmetricEncryption;
import org.apache.log4j.Logger;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class DeviceDaoUtil implements DaoUtil<DeviceEntity> {

    private JdbcUtilAdapter jdbcUtilAdapter;

    public DeviceDaoUtil(Class<?> clazz, Logger logger, Properties properties) {
        this.jdbcUtilAdapter = new JdbcUtilAdapter(clazz, logger, properties);
        try {
            createTable();
        } catch (SQLException throwables) {
            logger.debug("table already exists");
        }
    }

    public Connection getConnection(){
        Connection c = null;
        try {
            while (c == null || c.isClosed()) {
                c = jdbcUtilAdapter.getConnection();
                try {
                    Thread.sleep(500);
                }catch (Exception e){}
            }
        }catch (Exception ex){}
        return c;
    }

    public void returnConnection(Connection c) {
        jdbcUtilAdapter.returnConnection(c);
    }

    public void destory() {
        jdbcUtilAdapter.destory();
    }

    public void createTable() throws SQLException {
        Connection c = getConnection();
        Statement stmt = c.createStatement();
        if(jdbcUtilAdapter.getDbType() == JdbcUtilAdapter.DB_TYPE.SQLITE) {
            String sql = "CREATE TABLE device ("
                    + " id INTEGER primary key AUTOINCREMENT,"
                    + " unit_name CHAR(50) NOT NULL,"
                    + " device_id CHAR(600) NOT NULL UNIQUE,"
                    + " registe_time CHAR(50) DEFAULT NULL"
                    + " );";
            stmt.executeUpdate(sql);
        }
        if(jdbcUtilAdapter.getDbType() == JdbcUtilAdapter.DB_TYPE.MYSQL) {
            String sql = "CREATE TABLE device ("
                    + " id INTEGER primary key AUTO_INCREMENT,"
                    + " unit_name VARCHAR(50) NOT NULL,"
                    + " device_id VARCHAR(600) NOT NULL UNIQUE,"
                    + " registe_time VARCHAR(50) DEFAULT NULL"
                    + " );";
            stmt.executeUpdate(sql);
        }
        stmt.close();
        returnConnection(c);
    }

    /**
     * 添加记录
     *
     * @param deviceEntity
     * @throws SQLException
     */
    public void add(DeviceEntity deviceEntity) throws SQLException {
        Connection c = getConnection();
        Statement stmt = c.createStatement();
        c.setAutoCommit(false);
        StringBuffer sb = new StringBuffer();
        String id = deviceEntity.getId() != 0 ? String.valueOf(deviceEntity.getId()) : "NULL";
        sb.append("INSERT INTO device (id, unit_name, device_id, registe_time) "
                + "VALUES (" + id + ", '" + deviceEntity.getUnitName() + "', '" + deviceEntity.getDeviceId() + "', '" + deviceEntity.getRegisteTime() + "');");
        stmt.execute(sb.toString());
        c.commit();
        stmt.close();
        returnConnection(c);
    }

    /**
     * 判断是否存在某个设备
     * @param deviceEntity
     * @return
     * @throws SQLException
     */
    public boolean has(DeviceEntity deviceEntity) throws SQLException {
        Connection c = getConnection();
        Statement stmt = c.createStatement();
        boolean flag = false;
        ResultSet rs = stmt.executeQuery("SELECT count(1) AS num FROM device WHERE device_id='" + deviceEntity.getDeviceId() + "';");
        if(rs.next()){
            flag = rs.getInt("num") > 0;
        }
        rs.close();
        stmt.close();
        returnConnection(c);
        return flag;
    }

    @Override
    public boolean has(DeviceEntity entity, String privateKey) throws SQLException {
        DeviceEntity newEntity = getEntity(entity, privateKey);
        if(newEntity != null){
            try {
                return newEntity.getDeviceId().equals(entity.getDeviceId());
            }catch (Exception e){
                return false;
            }
        }else{
            return false;
        }
    }

    /**
     * 管理员查询是否有此设备
     * @param deviceEntity
     * @return
     * @throws SQLException
     */
    public boolean hasAdmin(DeviceEntity deviceEntity) throws SQLException {
        return has(deviceEntity);
    }

    @Override
    public boolean hasAdmin(DeviceEntity entity, String privateKey) throws SQLException {
        return has(entity, privateKey);
    }

    /**
     * 获取实体
     * @param deviceEntity
     * @return
     * @throws SQLException
     */
    public DeviceEntity getEntity(DeviceEntity deviceEntity) throws SQLException {
        Connection c = getConnection();
        DeviceEntity result = null;
        Statement stmt = c.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM device WHERE device_id='" + deviceEntity.getDeviceId() + "';");
        if(rs.next()){
            result = new DeviceEntity();
            result.setId(rs.getInt("id"));
            result.setDeviceId(rs.getString("device_id"));
            result.setUnitName(rs.getString("unit_name"));
            result.setRegisteTime(rs.getString("registe_time"));
        }
        rs.close();
        stmt.close();
        returnConnection(c);
        return result;
    }

    @Override
    public DeviceEntity getEntity(DeviceEntity deviceEntity, String privateKey) throws SQLException {
        DeviceEntity result = null;
        List<DeviceEntity> deviceEntities = selectAll(privateKey);
        for (DeviceEntity entity : deviceEntities){
            if(entity.getDeviceId().equals(deviceEntity.getDeviceId())){
                result = entity;
                break;
            }
        }
        return result;
    }

    /**
     * 查询所有注册单位及设备
     *
     * @return
     * @throws SQLException
     */
    public List<DeviceEntity> selectAll() throws SQLException {
        Connection c = getConnection();
        Statement stmt = c.createStatement();
        List<DeviceEntity> deviceEntities = new ArrayList<DeviceEntity>();
        ResultSet rs = stmt.executeQuery("SELECT * FROM device ORDER BY id DESC;");
        while (rs.next()) {
            DeviceEntity entity = new DeviceEntity();
            entity.setId(rs.getInt("id"));
            entity.setUnitName(rs.getString("unit_name"));
            entity.setDeviceId(rs.getString("device_id"));
            entity.setRegisteTime(rs.getString("registe_time"));
            deviceEntities.add(entity);
        }
        rs.close();
        stmt.close();
        returnConnection(c);
        return deviceEntities;
    }

    @Override
    public List<DeviceEntity> selectAll(String privateKey) throws SQLException {
        Connection c = getConnection();
        Statement stmt = c.createStatement();
        List<DeviceEntity> deviceEntities = new ArrayList<DeviceEntity>();
        ResultSet rs = stmt.executeQuery("SELECT * FROM device ORDER BY id DESC;");
        while (rs.next()) {
            DeviceEntity entity = new DeviceEntity();
            entity.setId(rs.getInt("id"));
            entity.setUnitName(rs.getString("unit_name"));
            try {
                entity.setDeviceId(AsymmetricEncryption.decrypt(rs.getString("device_id"), privateKey));
            }catch (Exception e){
                entity.setDeviceId(rs.getString("device_id"));
            }
            entity.setRegisteTime(rs.getString("registe_time"));
            deviceEntities.add(entity);
        }
        rs.close();
        stmt.close();
        returnConnection(c);
        return deviceEntities;
    }

    /**
     * 更新数据
     *
     * @param deviceEntity
     * @throws SQLException
     */
    public void update(DeviceEntity deviceEntity) throws SQLException {
        Connection c = getConnection();
        Statement stmt = c.createStatement();
        c.setAutoCommit(false);
        String sql = "UPDATE device set unit_name = '" + deviceEntity.getUnitName() + "' and device_id = '" + deviceEntity.getDeviceId() + "' where ID=" + deviceEntity.getId() + ";";
        stmt.executeUpdate(sql);
        stmt.close();
        c.commit();
        returnConnection(c);
    }

    /**
     * 删除记录
     *
     * @param id
     * @throws SQLException
     */
    public void delete(int id) throws SQLException {
        Connection c = getConnection();
        Statement stmt = c.createStatement();
        c.setAutoCommit(false);
        String sql = "DELETE from device where ID=" + id + ";";
        stmt.execute(sql);
        stmt.close();
        c.commit();
        returnConnection(c);
    }
}
